from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from flask_login import UserMixin

# 创建一个数据库db对象
db = SQLAlchemy()

# 增加一个收藏与取消收藏表格，需要定义在User模型之前
favorites = db.Table('favorites',
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('goods_id', db.Integer, db.ForeignKey('goods.id'))
)

# 对应user表---->实体类
class User(db.Model,UserMixin):
    # 表名
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)
    student_id = db.Column(db.String(50))
    #所属院系
    department = db.Column(db.String(100))
    email = db.Column(db.String(100))
    phone = db.Column(db.String(20))

    # 增加一个收藏字段，与favorites表建立关联关系
    favorites = db.relationship('Goods',secondary=favorites,backref='favorited_by')

    # 头像地址
    avatar = db.Column(db.String(500))
    is_admin = db.Column(db.Boolean, default=False)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)

# 分类表的创建，一定要在商品表之前
class Category(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)

class Goods(db.Model):
    __tablename__ = 'goods'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    price = db.Column(db.Numeric(10, 2))
    image_url = db.Column(db.String(255))
    # 商品信息：九成新、八成新
    condition = db.Column(db.String(20))
    is_sold = db.Column(db.Boolean, default=False)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)

    #关联商品分类id与用户id
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    seller_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    # 绑定了2个对象
    category = db.relationship('Category')
    seller = db.relationship('User')

class Message(db.Model):
    __tablename__ = 'messages'
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # 关联外键
    goods_id = db.Column(db.Integer, db.ForeignKey('goods.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    goods = db.relationship('Goods')
    user = db.relationship('User')

# 消息中心---留言功能
class Notification(db.Model):
    __tablename__ = 'notifications'
    id = db.Column(db.Integer, primary_key=True)
    recipient_id = db.Column(db.Integer, db.ForeignKey('users.id'))  # 通知接收者（卖家）
    sender_id = db.Column(db.Integer, db.ForeignKey('users.id'))     # 发送者（留言者）
    message = db.Column(db.String(255))                              # 通知内容
    is_read = db.Column(db.Boolean, default=False)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)

    recipient = db.relationship('User', foreign_keys=[recipient_id], backref='notifications_received')
    sender = db.relationship('User', foreign_keys=[sender_id])